package com.webapp.model;

import java.sql.Date;
import java.sql.ResultSet;
import java.sql.Statement;
import java.sql.Time;
import java.util.ArrayList;


public class PostModel {
	
	private Statement stmt = DatabaseConnector.connect();
	private int size = 0;
	
	/**
	 * Method that connects to the database and gathers the data for a specific post
	 * @param postId the ID for the desired post
	 * @return the Post that was requested
	 */
	public Post getPost(int postId){
		ResultSet rs;
		try{
			//public Post(int circleId, int authorId, String textContent, String dater, int comCount, int post)
			rs = stmt.executeQuery("SELECT * FROM posts WHERE Post_Id = '" +postId+ "'");
			if(rs.next() != false){
				Time t = rs.getTime(2);
				Date d = rs.getDate(2);
				String postDate = d.toString().concat(" "+t.toString());
				Post post = new Post(rs.getInt(5), rs.getInt(6), rs.getString(3), postDate, rs.getInt(4), rs.getInt(1));

				rs.close();
				return post;
			}
			else
				return null;
		}catch(Exception ex){return null;}
	}
	
	/*
	 * Returns a list of ALL posts by a specific USER.
	 * ORDER BY: index 0 is most recent, index n is oldest
	 */
	public ArrayList<Post> getAllPosts(int userId){
		ResultSet rs;
		try{
			rs = stmt.executeQuery("SELECT * FROM posts WHERE Author = '" +userId+ "' ORDER BY Date ASC");
			ArrayList<Post> list = new ArrayList<Post>();
			
			//calculates the size
			while(rs.next())
				size++;
			rs.first();
			
			do{

				Time t = rs.getTime(2);
				Date d = rs.getDate(2);
				String postDate = d.toString().concat(" "+t.toString());
				Post post = new Post(rs.getInt(5), rs.getInt(6), rs.getString(3), postDate, rs.getInt(4), rs.getInt(1));

				
				list.add(post);
				
			}while(rs.next());
			
			
			rs.close();
			return list;
			
		}catch(Exception ex){return null;}
	}
	
	/*
	 * Returns a list of ALL posts by a specific CIRCLE.
	 * ORDER BY: index 0 is most recent, index n is oldest
	 */
	public ArrayList<Post> getPostsByCircle(int circleId){
		try{
			ResultSet rs = stmt.executeQuery("SELECT * FROM posts WHERE Circle = '" +circleId+ "' ORDER BY Date ASC");
			ArrayList<Post> list = new ArrayList<Post>();
			
			while(rs.next()){
				Time t = rs.getTime(2);
				Date d = rs.getDate(2);
				String postDate = d.toString().concat(" "+t.toString());
				Post post = new Post(rs.getInt(5), rs.getInt(6), rs.getString(3), postDate, rs.getInt(4), rs.getInt(1));
				
				list.add(post);

			}
			
			return list;
		}catch(Exception ex){return null;}
	}
	
	/*
	 * Returns the required number of pages based on the number of gathered posts
	 */
	public int numPages(){
		if(size == 0)
			return 0;
		else
			return ((size/15)+1);
	}
	
	/**
	 * Method that connects to the database and inserts a new Post into the 'posts' table
	 * @param newPost the post to be added
	 * @return returns TRUE of successful, otherwise FALSE
	 */
	public boolean createPost(Post newPost){
		try{
			stmt.executeUpdate("INSERT INTO posts (Post_Id, Date, Content, Comment_Count, Circle, Author) VALUES ('"+
					newPost.getPostId()+ "', '" +newPost.getDate()+ "', '" +newPost.getContent()+ "', '" +
					newPost.getCommentCount()+ "', '" +newPost.getCircle()+ "', '" +newPost.getAuthor()+ "')");
		}catch(Exception ex){return false;}
		
		return true;
	}
	
	/**
	 * Method that connects to the database, checks to see if a specific post exists already,
	 * and updates that post to the new desired post.
	 * @param updatedPost the new data which will update the previous post
	 * @return returns TRUE is successful, otherwise FALSE
	 */
	public boolean modifyPost(Post updatedPost){
		try{
			if((stmt.executeQuery("SELECT * FROM posts WHERE Post_Id = '" +updatedPost.getPostId()+ "'")).next()){
				stmt.executeUpdate("UPDATE posts SET Date = '" +updatedPost.getDate()+ "', Content = '" +
								updatedPost.getContent()+ "', Author = '" +updatedPost.getAuthor()+ 
								"' WHERE Post_Id = '" +updatedPost.getPostId()+ "'");
			}
			else
				return false;
		}catch(Exception ex){return false;}
		return true;
	}
	
	/**
	 * Method that connects to the database and removes an existing post
	 * @param postId the ID to the post that will be deleted
	 * @return returns TRUE if successful, otherwise false
	 */
	public boolean deletePost(int postId){
		try{
			stmt.executeUpdate("DELETE FROM posts WHERE Post_Id = '" +postId+ "'");
			stmt.executeUpdate("DELETE FROM user_likes_post WHERE Post = '" +postId+ "'");
			ResultSet rs = stmt.executeQuery("SELECT Comment_Id FROM comments WHERE Post = '" +postId+ "'");
			while(rs.next())
				stmt.executeUpdate("DELETE FROM user_likes_comment WHERE Comment = '" +rs.getInt(1)+ "'");
			stmt.executeUpdate("DELETE FROM comments WHERE Post = '" +postId+ "'");
		}catch(Exception ex){return false;}
		return true;
	}
	
	/**
	 * Method that connects to the database and inserts values for a specific post and user into the 
	 * 'like' table, showing a user likes a specific post
	 * @param postId the ID for the specific post
	 * @param userId the ID for the user who performed this action
	 * @return returns TRUE if successful, otherwise FALSE
	 */
	public boolean likePost(int postId, int userId){
		try{
			if((stmt.executeQuery("SELECT * FROM user_likes_post WHERE User = '" +userId+ "' AND Post = '" +postId+ "'")).next())
				return true;
			else{
				stmt.executeUpdate("INSERT INTO user_likes_post (User, Post) VALUES ('" +userId+ "', '" +postId+ "')");
				return true;
			}
		}catch(Exception ex){return false;}
	}
	
	/**
	 * Method that connects to the database and deletes an existing value within the 'like'
	 * table, essentially showing the user no longer likes this post
	 * @param postId the ID for the specific post
	 * @param customerId the ID for the user who performed this action
	 * @return returns TRUE if successful, otherwise FALSE
	 */
	public boolean unlikePost(int postId, int userId){
		try{
			if(!(stmt.executeQuery("SELECT * FROM user_likes_post WHERE User = '" +userId+ "' AND Post = '" +postId+ "'")).next())
				return true;
			else{
				stmt.executeUpdate("DELETE FROM user_likes_post WHERE User = '" +userId+ "' AND Post = '" +postId+ "'");
				return true;
			}
		}catch(Exception ex){return false;}
	}
	
	public int numberOfLikes(int postId){
		try{
			ResultSet rs = stmt.executeQuery("SELECT User FROM user_likes_post WHERE Post = '" +postId+ "'");
			int total = 0;
			while(rs.next())
				total++;
			
			return total;
		}catch(Exception ex){return 0;}
	}
}
